﻿var xlNone = -4142;
var xlPasteAll = -4104;
var xlUp = -4162;

function splitPayroll (path, addressBook) {
	var application = new ActiveXObject("Excel.Application");
	application.DisplayAlerts = false;

	try {
		var file = application.GetOpenFilename('Excel 97-2003 (*.xls),*.xls,Excel 2007+(*.xlsx),*.xlsx');
		if (file) {
			var ext = file.substr(file.lastIndexOf(".") + 1);
			
			application.Workbooks.Open(file);
			var srcbook = application.ActiveWorkbook;
			
			var sheet = srcbook.Worksheets(1);			
			sheet.Select();
			try {
				sheet.ShowAllData();
			} catch (e) {
			}
			
			var codecol, namecol;
			for (var j = 1; j <= 10; j++) {
				if (sheet.Cells(1, j).FormulaR1C1 == '职员代码') {
					codecol = j;
				}
				if (sheet.Cells(1, j).FormulaR1C1 == '职员姓名') {
					namecol = j;
				}
			}
			var rownum = sheet.Cells(65536, codecol).End(xlUp).Row;
			
			for (var i = 2, m = (new Date).getMonth() + 1; i <= rownum; i++) {
				application.Workbooks.Add();
				var destbook = application.ActiveWorkbook;
				
				copyFromEXCEL(srcbook, 1, 1);
				pasteFromEXCEL(destbook, 1, 1);
				
				copyFromEXCEL(srcbook, 1, i);
				pasteFromEXCEL(destbook, 1, 2);
				
				destbook.Activate();
				var name = destbook.Worksheets(1).Cells(2, namecol).FormulaR1C1;
				var code = destbook.Worksheets(1).Cells(2, codecol).FormulaR1C1;
				var destfile = path + "\\" + name + "(" + code + ")" + m + '月工资.' + ext;
				destbook.SaveAs(destfile);
				destbook.Close();
				
				echo("[" + destfile + "]保存成功");
			}
			
			var addressCount;
			visitAddressBook(addressBook, function (sheet, codecol, mailcol) {
				addressCount = sheet.Cells(65536, codecol).End(xlUp).Row - 1;
			});
			
			echo("\n======================================================================");
			echo("通讯录中共有" + addressCount + "个职员");
			echo("本次共拆分出" + (rownum - 1) + "个工资单文件");
			echo("工资单文件存放于[" + path + "]");
			echo("======================================================================\n");
		}
	} catch (e) {
		echo(e.message);
	}

	application.Quit();
}

function sendPayroll (folder, addressBook) {
	var files = getFiles(folder);
	if (!files) {
		folder = getFolder("请选择附件目录");	
		if (!folder) {
			return false;
		}
		files = getFiles(folder);
	}	
	if (!files) {
		return false;
	}

	var recipients = {};
	visitAddressBook(addressBook, function (sheet, codecol, mailcol) {
		var rownum = sheet.Cells(65536, codecol).End(xlUp).Row;
		for (var i = 2; i <= rownum; i++) {
			recipients['' + sheet.Cells(i, codecol).FormulaR1C1] = sheet.Cells(i, mailcol).FormulaR1C1;
		}
	});	
	if (!recipients) {
		return false;
	}

	var sender = sendPayroll.sender
	
	var configuration = getCDOConfiguration(sender);
	if (!configuration) {
		return false;
	}
				
	var successedFolder = folder + "\\successed";
	createFolder(successedFolder);
	
	var retry = 0, interval = 0, successed = 0, choice;

	for (var i = 0; i < files.length; i++) {
		var name = files[i].substr(folder.length + 1);

		var to = recipients[name.substring(name.indexOf("(") + 1, name.indexOf(")"))];
		if (!to) {
			echo("无法找到" + name + "对应的邮件地址");
			continue;
		}
				
		if (choice != 'a') {
			echo("\n======================================================================");
			choice = readLine("您是否要发送[" + name + "]到[" + to + "]\n(是,否,全是,全否[y,n,a,c])?").toLowerCase();
			if (choice == 'c') {
				break;
			}
			if (choice == 'n') {
				continue;
			}
			echo("");
		}
		try {
			var message = new ActiveXObject("CDO.Message");
			message.Configuration = configuration;
			message.From = sender.from;
			message.To = to; 
			message.Subject = name.substr(0, name.indexOf("(")) + "--" + sender.subject; 
			message.TextBody = sender.body;
			message.AddAttachment(files[i]);
			message.Send();

			moveFile(files[i], successedFolder + "\\" + name);
			
			echo("发送[" + name + "]到[" + to + "]成功！");
			
			retry = 0;			
			successed++;
		} catch (e) {
			//echo(e.message);
			if (e.message && e.message.indexOf("550") != -1) {
				echo("邮件地址[" + to + "]不存在，请检查通讯录");
				continue;
			}
			echo("\n发送[" + name + "]到[" + to + "]失败！");
			echo("请检查如下问题：");
			echo("1.网络是否畅通");
			echo("2.防火墙是否允许外发邮件");
			echo("3.邮件服务器是否限制发信频率\n");

			if (interval > sender.timeout) {
				echo("重试超时，程序即将退出，请您确认解决上述问题后再次启动程序\n");
				break;
			} else if (retry == 0) {
				interval = sender.interval;
			} else {
				interval *= 2;
			}
			retry++;
			
			echo(interval + "秒后将重试，请耐心等待...\n");
			WScript.Sleep(interval * 1000);
			
			configuration = getCDOConfiguration(sender);
			
			i--;
		}
	}
	
	echo("\n======================================================================");
	echo("[" + folder + "]下共" + files.length + "个文件");
	echo("成功发送出" + successed + "个文件");
	echo("发送成功的文件已移动到了[" + successedFolder + "]");
	echo("======================================================================\n");
}

function visitAddressBook (addressBook, visit) {
	var application = new ActiveXObject("Excel.Application");
	try {
		application.Workbooks.Open(addressBook);
		var sheet = application.ActiveWorkbook.Worksheets(1);
		
		var codecol, mailcol;
		for (var j = 1; j <= 10; j++) {
			if (sheet.Cells(1, j).FormulaR1C1 == '职员代码') {
				codecol = j;
			}
			if (sheet.Cells(1, j).FormulaR1C1 == '邮件地址') {
				mailcol = j;
			}
		}

		if (codecol && mailcol) {
			visit(sheet, codecol, mailcol);
		}	
	} catch (e) {
		echo(e.message);
	}
	application.Quit();
}
		
		
		
function getCDOConfiguration (sender) {
	try {
		var configuration = new ActiveXObject("CDO.Configuration");
		configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2; 
		configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = sender.smtpserver; 
		configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = sender.smtpserverport; 
		configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = true;
		configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusername") = sender.username;
		configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendpassword") = sender.password;
		configuration.Fields.Update();

		return configuration;
	} catch (e) {
		echo("无法创建CDO组件");
	}
	
	return null;
}

function getFolder (title) {
	var application = new ActiveXObject('Shell.Application');
	
	var folder = application.BrowseForFolder(0, title, 0);
	if (!folder) {
		return null;
	}
	
	return folder.Self.Path;
}

function createFolder (folder) {
	try {
		var fso = new ActiveXObject("Scripting.FileSystemObject");
		
		fso.CreateFolder(folder);
	} catch (e) {
		// ignore
	}
}

function getFiles (path) {
	var files = [];
	
	try {
		var fso = new ActiveXObject("Scripting.FileSystemObject");
		
		for (var itr = new Enumerator(fso.GetFolder(path).Files); !itr.atEnd(); itr.moveNext()) {
			files.push(itr.item().Path);
		}
	} catch (e) {
		// ignore
	}

	return files.length == 0 ? null: files;
}

function moveFile (from, to) {
	try {
		var fso = new ActiveXObject("Scripting.FileSystemObject");
		
		fso.MoveFile(from, to);
	} catch (e) {
		// ignore
	}
}

function copyFromEXCEL (book, name, r1, c1, r2, c2) {
	var rng = getRangeFromEXCEL(book, name, r1, c1, r2, c2);
	rng.Select();
	rng.application.Selection.Copy();		
}

function pasteFromEXCEL (book, name, r1, c1, r2, c2) {
	var rng = getRangeFromEXCEL(book, name, r1, c1, r2, c2);
	rng.Select();
	rng.Worksheet.Paste();
	rng.application.CutCopyMode = false;
}

function getRangeFromEXCEL (book, name, r1, c1, r2, c2) {
	book.Activate();

	var sheet = book.Worksheets(name);
	sheet.Select();

	if (r1 && c1 && r2 && c2) {
		return sheet.Range(sheet.Cells(r1, c1), sheet.Cells(r2, c2));
	}
	
	if (r1 && c1 && !r2 && !c2) {
		return sheet.Cells(r1, c1);
	}
	
	if (r1 && c1 && !r2 && c2) {
		return sheet.Range(sheet.Cells(r1, c1), sheet.Cells(sheet.Cells(65536, 1).End(xlUp).Row, c2));
	}
			
	if (r1 && !c1 && !r2 && !c2) {
		return sheet.Rows(r1);
	}

	if (r1 && !c1 && r2 && !c2) {
		return sheet.Rows(r1 + ':' + r2);
	}

	if (!r1 && c1 && !r2 && c2) {
		return sheet.Columns(c1 + ':' + c2);
	}
		
	if (!r1 && !c1 && r2 && c2) {
		return sheet.Cells(r2, c2);
	}

	return sheet.Cells;	
}

function readLine (prompt) {
	var stdin = WScript.StdIn;

	echo(prompt);
	return stdin.ReadLine();
}

function echo(line) {
	WScript.Echo(line);
}
